﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Streaming_Service.SQL_Ting {
    using System.Data;

    using Streaming_Service.Enum;
    using Streaming_Service.Media;
    using Streaming_Service.User;

    public class RatingQueries {
        public static bool RateMovie(UserSession user, int movieId, Rating rating, string comment, SqlHandler sh) {
            if (rating == Rating.Wrong || !MovieQueries.MovieExists(movieId, sh)) return false;
            
            if(RatingExists(user, movieId, sh)) {
                return EditRating(user, movieId, rating, comment, sh);
            }

            var SQL =
                "INSERT INTO rating (userid, movieid, rating, comment)" +
                "VALUES(@userid, @movieid, @rating, @comment)";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("userid", SqlDbType.Int).Value = user.UserId;
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;
            c.Parameters.Add("rating", SqlDbType.Int).Value = rating;
            c.Parameters.Add("comment", SqlDbType.Text).Value = comment ?? "";

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool RatingExists(UserSession user, int movieId, SqlHandler sh) {
            var SQL = "SELECT userid FROM rating WHERE userid = @userid AND movieid = @movieid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("userid", SqlDbType.Int).Value = user.UserId;
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r > 0;
            }
            return false;
        }

        public static bool EditRating(UserSession user, int movieId, Rating rating, string comment, SqlHandler sh) {
            if (rating == Rating.Wrong || !RatingExists(user, movieId, sh)) return false;

            var SQL =
                "UPDATE rating SET rating = @rating, comment = @comment WHERE userid = @userid AND movieid = @movieid";

            var c = sh.CreateCommand(SQL);

            c.Parameters.Add("rating", SqlDbType.Int).Value = rating;
            c.Parameters.Add("comment", SqlDbType.Text).Value = comment ?? "";
            c.Parameters.Add("userid", SqlDbType.Int).Value = user.UserId;
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool DeleteRating(UserSession user, int movieId, SqlHandler sh) {
            if (!RatingExists(user, movieId, sh)) return false;
            var SQL = "DELETE FROM rating WHERE userid = @userid AND movieid = @movieid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("userid", SqlDbType.Int).Value = user.UserId;
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static List<RatingEntry> ListRatings(SqlHandler sh) {
            var SQL = "SELECT * FROM rating";

            var ratings = new List<RatingEntry>();

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteReader();
            while(r.Read()) {
                ratings.Add(new RatingEntry {
                    UserId = r.GetInt32(r.GetOrdinal("userid")),
                    MovieId = r.GetInt32(r.GetOrdinal("movieid")),
                    Rating = ReadRating(r.GetInt32(r.GetOrdinal("rating"))),
                    Comment = r.GetString(r.GetOrdinal("comment"))
                });
            }
            r.Close();

            return ratings;
        }

        public static List<RatingEntry> UserRatings(int userId, SqlHandler sh) {
            var SQL = "SELECT * FROM rating WHERE userid = @userid";

            var ratings = new List<RatingEntry>();

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("userid", SqlDbType.Int).Value = userId;

            var r = c.ExecuteReader();
            while (r.Read()) {
                ratings.Add(new RatingEntry {
                    UserId = r.GetInt32(r.GetOrdinal("userid")),
                    MovieId = r.GetInt32(r.GetOrdinal("movieid")),
                    Rating = ReadRating(r.GetInt32(r.GetOrdinal("rating"))),
                    Comment = r.GetString(r.GetOrdinal("comment"))
                });
            }
            r.Close();

            return ratings;
        }

        public static List<RatingEntry> MovieRatings(int movieId, SqlHandler sh) {
            var SQL = "SELECT * FROM rating WHERE movieid = @movieid";

            var ratings = new List<RatingEntry>();

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;

            var r = c.ExecuteReader();
            while (r.Read()) {
                ratings.Add(new RatingEntry {
                    UserId = r.GetInt32(r.GetOrdinal("userid")),
                    MovieId = r.GetInt32(r.GetOrdinal("movieid")),
                    Rating = ReadRating(r.GetInt32(r.GetOrdinal("rating"))),
                    Comment = r.GetString(r.GetOrdinal("comment"))
                });
            }
            r.Close();

            return ratings;
        }
 
        private static Rating ReadRating(int j) {
            switch (j) {
                case 0:
                    return Rating.Zero;
                case 1:
                    return Rating.One;
                case 2:
                    return Rating.Two;
                case 3:
                    return Rating.Three;
                case 4:
                    return Rating.Four;
                case 5:
                    return Rating.Five;
                default:
                    return Rating.Wrong;
            }
        }
    }
}